package  ch14.d1;

import ch14.domain.User;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCTest {

    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        User u=new User();
        u.setUserid("1001");
        u.setName("张三");
        u.setEname("zhangsan");
        u.setPersonId("123456789012345678");
        u.setCredit(100);
        u.setSex("男");
        u.setCity("北京");
        this.insert(u);
    }

    public void insert(User user) throws SQLException, ClassNotFoundException {
        Connection conn=getConn();

        String sql="insert into user(userid,name,ename,personId,credit,city,sex ) " +
                "values(?,?,?,?,?,?,?)"; //? 占位符
        PreparedStatement stmt=conn.prepareStatement(sql);
        // 给占位符赋值
        stmt.setString(1,user.getUserid());
        stmt.setString(2,user.getName());
        stmt.setString(3,user.getEname());
        stmt.setString(4,user.getPersonId());
        stmt.setInt(5,user.getCredit());
        stmt.setString(6,user.getCity());
        stmt.setString(7,user.getSex());

        int n=stmt.executeUpdate();

        stmt.close();
        conn.close();
    }

    public void update(User user) throws SQLException, ClassNotFoundException {
        Connection conn=getConn();

        String sql="update user set name=?,ename=?,city=? where userid=?"; //? 占位符
        PreparedStatement stmt=conn.prepareStatement(sql);
        // 给占位符赋值

        stmt.setString(1,user.getName());
        stmt.setString(2,user.getEname());
        stmt.setString(3,user.getCity());
        stmt.setString(4,user.getUserid());

        int n=stmt.executeUpdate();

        stmt.close();
        conn.close();
    }

    public void delete(String userid) throws SQLException, ClassNotFoundException {
        Connection conn=getConn();

        String sql="delete from user where userid=?"; //? 占位符
        PreparedStatement stmt=conn.prepareStatement(sql);
        // 给占位符赋值

        stmt.setString(1,userid);

        int n=stmt.executeUpdate();

        stmt.close();
        conn.close();
    }

    @Test
    public void testDelete() throws SQLException, ClassNotFoundException {
        delete("1001");
    }


    public void testCommit() throws SQLException, ClassNotFoundException {
        Connection conn=getConn();

        conn.setAutoCommit(false); // 关闭自动提交 事务开始
        try {
            //删除
            String sql = "delete from user where userid=100"; //? 占位符
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();

            //新增
            String sql2 = "insert "; //? 占位符
            PreparedStatement stmt2 = conn.prepareStatement(sql);
            stmt2.executeUpdate();

            //修改
            String sql3 = "update "; //? 占位符
            PreparedStatement stmt3 = conn.prepareStatement(sql);
            stmt3.executeUpdate();

            conn.commit(); // 提交事务

        }catch (Exception e) {
            conn.rollback(); // 回滚
        }finally{
            conn.close();
        }

    }

    //批量操作 1000w  500
    public void batchInsert(List<User> list) throws SQLException, ClassNotFoundException {
        Connection conn=getConn();

        String sql="insert into user(userid,name,ename,personId,credit,city,sex ) " +
                "values(?,?,?,?,?,?,?)"; //? 占位符
        PreparedStatement stmt=conn.prepareStatement(sql);//大巴车
        for (int i = 0; i < list.size(); i++) {
            User user=list.get(i);

            // 给占位符赋值
            stmt.setString(1,user.getUserid());
            stmt.setString(2,user.getName());
            stmt.setString(3,user.getEname());
            stmt.setString(4,user.getPersonId());
            stmt.setInt(5,user.getCredit());
            stmt.setString(6,user.getCity());
            stmt.setString(7,user.getSex());

            stmt.addBatch(); // 添加到批处理中 上车
            if( (i+1)%50==0 ){
                stmt.executeBatch(); // 执行批处理 发车
                stmt.clearBatch(); // 清空批处理
            }
        }
        stmt.executeBatch(); // 执行批处理 发车

        conn.close();
    }

    @Test
    public void test存储过程() throws SQLException, ClassNotFoundException {
        Connection conn=getConn();
        CallableStatement stmt=conn.prepareCall("call myproc4_out(?,?)");
        // 给占位符赋值 输入参数
        stmt.setString(1,"100");
        // 注册输出参数 输出参数
        stmt.registerOutParameter(2, Types.INTEGER);
        stmt.execute();
        // 获取输出参数
        int n=stmt.getInt(2);
        System.out.println(n);
        stmt.close();
        conn.close();
    }
    @Test
    public void test存储过程2() throws SQLException, ClassNotFoundException {
        Connection conn=getConn();
        CallableStatement stmt=conn.prepareCall("call myproc5_inout(?)");
        // 给占位符赋值 输入参数
        stmt.setInt(1,100);
        // 注册输出参数 输出参数
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.execute();
        // 获取输出参数
        int n=stmt.getInt(1);
        System.out.println(n);
        stmt.close();
        conn.close();
    }

    public Connection getConn() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2. 获取连接
        String url="jdbc:mysql://localhost:3306/javakc107?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
        return DriverManager.getConnection(url,"root","123456");

    }
    @Test
    public void testSelect() throws SQLException, ClassNotFoundException {
        Connection conn=getConn();
        Statement stmt=conn.createStatement();

        // ResultSet:结果集
        ResultSet rs=stmt.executeQuery("select userid,name,ename,personId,credit,city,sex from user");

        //ResultSet -->List
        //循环取出结果集中的数据
        List<User> list=new ArrayList<>();
        while(rs.next()){ // next: 判断是否有一条数据
            String userid=rs.getString("userid");
            String name=rs.getString("name");
            String ename=rs.getString("ename");
            String personId=rs.getString("personId");
            int credit=rs.getInt("credit");
            String city=rs.getString("city");
            String sex=rs.getString("sex");

            User user=new User();
            user.setUserid(userid);
            user.setName(name);
            user.setEname(ename);
            user.setPersonId(personId);
            user.setCredit(credit);
            user.setSex(sex);
            user.setCity(city);

            list.add(user);

            System.out.println(user);
        }

        rs.close();
        stmt.close();
        conn.close();

        for(User user:list){
            System.out.println(user);
        }


    }








}
